import pymysql

db = pymysql.connect(host='wlff.site',user='remoteroot',port=3307,password='213471284',database='Book_management')

def select(bookid):
    cursor = db.cursor()

    select_list = "SELECT book_id, book_num,book_surplus FROM book_info WHERE book_id =%s ; " %bookid
    if cursor.execute(select_list) == 1:
        data = cursor.fetchall()
        result = []
        for row in data:
            book_id = row[0]
            borrow_count = row[1] - row[2]
            result.append({"书籍编号": book_id, "借阅数量总和": borrow_count})
        return result
    else:
        return "该书籍未被借出"

def select_list(bkid):
    cursor = db.cursor()
    select_list2 = "SELECT b.id, b.book_id, b.borrow_num, b.user_id, b.borrow_time, b.revert_book FROM book_borrow b JOIN ( SELECT book_id FROM book_info WHERE book_id = %s ) AS subquery ON b.book_id = subquery.book_id" % (bkid)
    if cursor.execute(select_list2) == 1:
        data_list = cursor.fetchall()
        result1 = []
        for row in data_list:
            book_id = row[1]
            borrow_num = row[2]
            user_id = row[3]
            borrow_time = row [4]
            revert_book = row [5]
            result1.append(
            {"书籍编号": book_id, "借阅数量": borrow_num, "用户id": user_id, "借阅时间": borrow_time,
             "是否归还": revert_book})
        return result1
    else:
        return "您指定的图书，没有被借出"
